# -*- coding: UTF-8 -*-

'''
Created on Jun 20, 2016

@author: pennix
'''

from configparser import ConfigParser, ExtendedInterpolation
from logging import getLogger, DEBUG, INFO
from os import environ
from os.path import abspath, dirname, join, splitext
from re import sub
from sys import argv
from typing import Iterable
from mysql.connector import connect
from mysql.connector.cursor import MySQLCursor
from mysql.connector.pooling import PooledMySQLConnection

def formatsql(sql):
    ''' format sql '''
    formatted = sub(r'\s+', ' ', sql)
    formatted = sub(r'\(\s+', '(', formatted)
    formatted = sub(r'\s+\)', ')', formatted)
    formatted = sub(r'\s*,\s*', ', ', formatted)
    return formatted.strip()

class QueryRunner(object):
    ''' classdocs '''

    def __init__(self, name, **kwargs):
        self.logger = getLogger(__name__)

        config = ConfigParser(interpolation=ExtendedInterpolation(), empty_lines_in_values=False)
        configfiles = (
            splitext(__file__)[0] + '.cfg',
            join(dirname(argv[0]), 'mysql.cfg'),
            join(environ['HOME'], 'mysql.cfg')
        )
        for configFile in configfiles:
            self.logger.info('Reading config from %s', abspath(configFile))
        config.read(configfiles)

        if not config.has_section(name):
            raise ValueError('No Configuration Found for ' + name)

        args = {}
        args.update(config[name])
        if kwargs:
            args.update(kwargs)

        for key in args.keys():
            value = args[key]
            if isinstance(value, str) and len(value) > 0:
                if value == 'True' or value == 'False':
                    args[key] = bool(value)
                elif value.isdigit():
                    args[key] = int(value)

        if self.logger.isEnabledFor(DEBUG):
            for key, value in args.items():
                self.logger.debug(
                    '%s config: %s=%s(%s)',
                    name,
                    key,
                    '********' if key == 'password' else value,
                    type(value)
                )

        if not 'pool_name' in args:
            args['pool_name'] = name

        self.conn: PooledMySQLConnection = connect(pool_reset_session=False, **args)
        self.logger.info('Connected to MySQL Server %s', args['pool_name'])
        self.cursor: MySQLCursor = self.conn.cursor()

    def __enter__(self):
        return self

    def __exit__(self, exc_type, exc_value, traceback):
        if hasattr(self, 'cursor'):
            self.cursor.close()
            del self.cursor

        if hasattr(self, 'conn'):
            if self.conn.is_connected():
                self.conn.close()
                self.logger.info('Disconnected from MySQL Server')
            del self.conn

    def fetchdict(self, sql: str, *params) -> dict:
        return self.fetchone(sql, *params, fmt=dict)

    def fetchtuple(self, sql: str, *params) -> tuple:
        return self.fetchone(sql, *params, fmt=tuple)

    def fetchone(self, sql: str, *params, fmt=tuple):
        ''' fetch first record only '''
        rows = self.fetchall(sql, *params, fmt=fmt, limit=1)
        return rows[0] if rows else None

    def fetchall(self, sql: str, *params, fmt=tuple, limit=0):
        ''' fetch all records '''
        self.update(sql, *params)
        rows = self.cursor.fetchall()
        if not rows:
            return None
        if limit > 0:
            rows = rows[:limit]

        if fmt == tuple:
            return rows
        elif fmt == dict:
            columnCount = len(self.cursor.description)
            fields = [ i[0] for i in self.cursor.description ]
            all = []
            for row in rows:
                data = {}
                for i in range(columnCount):
                    data[fields[i]] = row[i]
                all.append(data)
            return all
        else:
            raise RuntimeError('Unsupported format {}'.format(format))

    def insert(self, sql: str, *params):
        ''' insert a dict '''
        if len(params) == 1 and isinstance(params[0], dict):
            pairs: dict = params[0]
            sql = 'INSERT INTO {} (`{}`) VALUES ({})' \
                .format(sql, '`, `'.join(pairs.keys()), ', '.join(['%s'] * len(pairs)))
            params = tuple(pairs.values())
        self.update(sql, *params)
        return self.cursor.lastrowid

    def update(self, sql: str, *params):
        ''' execute update '''
        if self.logger.isEnabledFor(INFO):
            self.logger.info('Executing %s', formatsql(sql))

        if len(params) == 1 and isinstance(params[0], tuple):
            params = params[0]

        if params:
            if self.logger.isEnabledFor(INFO):
                self.logger.info('Params %s', params)
            self.cursor.execute(sql, params)
        else:
            self.cursor.execute(sql)
        return self.cursor.rowcount

    def executemany(self, sql: str, params: Iterable[Iterable]):
        ''' execute many'''
        if self.logger.isEnabledFor(INFO):
            self.logger.info('Executing %s', formatsql(sql))

        self.logger.debug('Params Count %s', len(params))
        self.cursor.executemany(sql, params)
        return

    def start_transaction(self):
        ''' start transaction '''
        self.conn.start_transaction()
        self.logger.debug('Transaction Started')

    def commit(self):
        ''' commit transaction '''
        self.conn.commit()
        self.logger.debug('Transaction Committed')

    def rollback(self):
        ''' rollback transaction '''
        self.conn.rollback()
        self.logger.debug('Transaction Rolled Back')
